/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var ItbPartnerApplicationModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  //`organize` varchar(100) NOT NULL DEFAULT '' COMMENT '机构名称',
  //`name` varchar(100) NOT NULL DEFAULT '' COMMENT '提交申请者的名称',
  //`mobile` varchar(100) DEFAULT NULL COMMENT '电话',
  //`email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  //`qq` varchar(100) DEFAULT NULL COMMENT '邮箱',
  //`state` int(3) NOT NULL DEFAULT '0' COMMENT '状态: 10新建 20审核中 30完成',
  //`user_id` bigint(20) NOT NULL COMMENT '用户id',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL DEFAULT '',
  //`del_flg` int(1) NOT NULL DEFAULT '0' COMMENT '删除flg',


};
ItbPartnerApplicationModel.prototype = new ItbBaseModel();

// 取得表名
ItbPartnerApplicationModel.prototype.getTableName = function(){
    return 'itb_partner_application';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbPartnerApplicationModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'id='+this.id;
}

// 插入申请
// 参数: hourLimit 时间限制，不能插入太频繁(单位: 秒)
// 参数: callback:参数如下
//          error:
//          insertId:新建的id，失败返回null
ItbPartnerApplicationModel.prototype.insertPartnerInHour = function(hourLimit, callback){
    var THIS = this;
    var tableName = this.getTableName();

    // user_id必须存在
    if(!THIS.user_id){
        return callback(error, null);
    }
    // 创建sql
    function makeSql(THIS){
        var sql0 = "";
        var sql1 = "";

        // 建立sql所需部分
        for (var key in THIS){
            var value = THIS[key];
            if(value!=null && (typeof value!=='function')){
                sql0+=(key+",");
                if(typeof value=="string"){
                    sql1+=("'"+value+"',");
                }else{
                    sql1+=(value+",");
                }

            }
        }

        // 补充默认信息
        sql0 += "create_time,update_time,update_by,del_flg";
        sql1 += "sysdate(),sysdate(),"+THIS.user_id+",0";

        var sql =
            " INSERT INTO itb_partner_application  "+
            " 		("+sql0+") "+
            " SELECT "+sql1+
            " FROM DUAL "+
            " WHERE NOT EXISTS "+
            "     ( SELECT id "+
            "       FROM itb_partner_application "+
            "       WHERE create_time>date_add(sysdate(), interval -" + hourLimit +" second) "+
            "       AND user_id=  "+ THIS.user_id+
            "       AND del_flg=0 ) ";
        return sql
    }
    // sql
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerApplicationModel.insertPartnerInHour Err:'+err);
        }

        // Use the connection
        var sql =  makeSql(THIS);

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if (error){
                console.log(error);
                return callback(error, null);
            }
            if(!result ){
                return callback(null, null);
            }

            callback(error, result.insertId);
        });
    });
}

//------------------------------------------------------------------
/* 检查是否为合法城市合伙人
 * 参数:userId
 * 参数:callback: true/false
 * */
ItbPartnerApplicationModel.prototype.isValidCityPartner = function(userId,callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
            " select p.partner_id " +
            " FROM itb_partner_application a, itb_partner p, itb_user u " +
            " WHERE a.user_id= "+userId+
            " AND a.del_flg=0 AND a.type=20 AND a.state=40 " +
            " AND p.del_flg=0 AND p.apply_id=a.id AND p.user_id=a.user_id " +
            " AND u.del_flg=0 AND u.user_id=p.user_id ";

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            var ret = false;
            if(result && result.length>0) {
                ret = true;
            }
            callback(error, ret);
        });
    });
};

//------------------------------------------------------------------
/* 获取城市合伙人状态信息
 * 参数:userId
 * 参数:callback:回调
 * */
ItbPartnerApplicationModel.prototype.getPartnerState = function(userId,callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
            " select p.* " +
            " FROM itb_partner_application p " +
            " WHERE p.user_id= "+userId+
            " AND p.del_flg=0 " +
            " AND p.type=20 ";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    });
}


//===================================提交申请相关==================================
/* 将当前的del_flg改为0
 * 参数:userId
 * 参数:callback:回调
 * */
ItbPartnerApplicationModel.prototype.SetDel_flgIs_1 = function(userId,callback){
    pool.pool.getConnection(function(err, connection) {
        //异常保护
        if (err) {
            console.log('ItbPartnerApplicationModel.SetDel_flgIs_1 Err:'+err);
            return callback(err, null);
        }
        //当前state == 40的时候讲当前user_id 的del_flg改为1
        var sql =
            " UPDATE itb_partner_application " +
            " SET del_flg = 1 " +
            " WHERE user_id = "+userId
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    })
}


///* 插入申请人填入的信息
// * 参数:userId  申请人唯一标识
// * 参数:info    申请人填入的信息
// * 参数:callback:回调
// * */
//ItbPartnerApplicationModel.prototype.InsertPartnerApplication = function(userId,info,callback){
//    var THIS = this;
//    var tableName = this.getTableName();
//    var info = JSON.parse(info);
//
//    // 创建sql
//    function makeSql(THIS){
//        var sql0 = "";
//        var sql1 = "";
//
//        // 建立sql所需部分
//        for (var key in THIS){
//            var value = THIS[key];
//            if(value!=null && (typeof value!=='function')){
//                sql0+=(key+",");
//                if(typeof value=="string"){
//                    sql1+=("'"+value+"',");
//                }else{
//                    sql1+=(value+",");
//                }
//            }
//        }
//
//        var sql =
//            " INSERT INTO itb_partner_application  "+
//            " 		("+sql0+") "+
//            " SELECT "+sql1+
//            " FROM DUAL "+
//            " WHERE NOT EXISTS "+
//            "     ( SELECT id "+
//            "       FROM itb_partner_application "+
//            "       WHERE create_time>date_add(sysdate(), interval -" + hourLimit +" second) "+
//            "       AND user_id=  "+ THIS.user_id+
//            "       AND del_flg=0 ) ";
//        return sql
//    }
//    pool.pool.getConnection(function(err, connection) {
//        //异常保护
//        if (err) {
//            console.log('ItbPartnerApplicationModel.InsertApplication Err:'+err);
//            return callback(err, null);
//        }
//
//        //将当前的用户申请信息插入导数据库中
//        var sql = makeSql(THIS);
//        console.log(sql);
//        connection.query(sql, function(error, result) {
//            // And done with the connection.
//            connection.release();
//            callback(error, result);
//        });
//    })
//}
//------------------------------------------------------------------
/**
 * 获取城市合伙人状态信息
 * 参数 userId: 用户唯一标识
 * 参数 callback: 回调
 * */
ItbPartnerApplicationModel.prototype.getCityPartnerInfoEx_v5_4 = function(userId,callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
            ' select m.*, '+
            ' 	a_p.`name` as prov_text,   '+
            ' 	a_c.`name` as city_text,   '+
            ' 	a_d.`name` as dist_text '+
            ' from ( '+
            '         select pa.name, pa.prov, pa.city, pa.dist ,pa.detail, pa.bank, pa.`bank_account` as bankAccount '+
            '         from `itb_partner_application` pa '+
            '         where pa.`user_id`= '+userId+
            '         and pa.del_flg=0 '+
            '     ) m,  '+
            '     `itb_area` a_p,  '+
            '     `itb_area` a_c,  '+
            '     `itb_area` a_d '+
            ' where m.`prov`=a_p.`id` '+
            ' and  a_p.del_flg=0 '+
            ' and m.`city`=a_c.`id` '+
            ' and  a_c.del_flg=0 '+
            ' and m.`dist`=a_d.`id`	 '+
            ' and  a_d.del_flg=0 ';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();


            if(result && result.length>0) {
                return callback(error, result[0]);
            }
            return callback(error, null);
        });
    });
};

//------------------------------------------------------------------
/**
 * 更新合伙人状态信息
 * 参数 userId
 * 参数 info
 * 参数 callback: 回调
 * */
ItbPartnerApplicationModel.prototype.updateCityPartnerInfo_0_v5_4 = function(userId,info, callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
            " update itb_partner_application pa "+
            " set pa.name='"+info.name+"', "+
            "	pa.prov="+info.cityInfo.prov.id+", "+
            "	pa.city="+info.cityInfo.city.id+", "+
            "	pa.dist="+info.cityInfo.dist.id+", "+
            "	pa.detail='"+info.cityInfo.detail+"', "+
            "	pa.bank='"+info.bank+"', "+
            "	pa.`bank_account`='"+info.bankAccount+"', "+
            "	pa.`update_time`=sysdate() "+
            " where pa.`user_id`= "+ userId+
            " and pa.type=20 "+
            " and pa.del_flg=0 ";

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();


            if(error) {
                return callback(error);
            }
            return callback(null);
        });
    });
};

//------------------------------------------------------------------
/**
 * 更新合伙人状态信息
 * 参数 userId
 * 参数 info
 * 参数 callback: 回调
 * */
ItbPartnerApplicationModel.prototype.updateCityPartnerInfo_1_v5_4 = function(userId,info, callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
            " update itb_partner p "+
            " set p.name='"+info.name+"', "+
            "	p.bank='"+info.bank+"', "+
            "	p.`bank_account`='"+info.bankAccount+"', "+
            "	p.`update_time`=sysdate() "+
            " where p.`user_id`="+ userId+
            " and p.del_flg=0 ";

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();


            if(error) {
                return callback(error);
            }
            return callback(null);
        });
    });
};


// 获取已存在合伙人的(申请)地区
ItbPartnerApplicationModel.prototype.getPartnerExistArea = function(callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT distinct a.prov, a.city, a.dist '+
            ' FROM itb_partner_application a, itb_partner p '+
            ' WHERE a.type=20 '+// 合伙人申请
            ' AND a.state=40 '+// 申请通过
            ' AND a.del_flg=0 '+
            ' AND p.apply_id=a.id '+// 存在合伙人
            ' AND p.del_flg=0 ';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(error){
                console.log(error);
            }
            return callback(error, result);
        });
    });
};

// 获取合伙人申请地址
ItbPartnerApplicationModel.prototype.getPartnerAppArea = function(userId, callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = ' SELECT x.prov, x.city, x.dist, '+
                '          a1.name AS prov_name, a2.name AS city_name, a3.name AS dist_name '+
                ' FROM ('+
                '   SELECT distinct a.prov, a.city, a.dist '+
                '   FROM itb_partner_application a, itb_partner p '+
                '   WHERE a.type=20 '+// 合伙人申请
                '   AND a.state=40 '+// 申请通过
                '   AND a.del_flg=0 '+
                '   AND p.apply_id=a.id '+// 存在合伙人
                '   AND p.user_id='+userId+
                '   AND p.del_flg=0 ) x '+
                ' LEFT JOIN itb_area a1 ON a1.del_flg=0 AND a1.id=x.prov '+
                ' LEFT JOIN itb_area a2 ON a2.del_flg=0 AND a2.id=x.city '+
                ' LEFT JOIN itb_area a3 ON a3.del_flg=0 AND a3.id=x.dist ';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(error){
                console.log(error);
            }
            return callback(error, result);
        });
    });
};



//--------------------------------end-------------------------------

// 导出
module.exports = ItbPartnerApplicationModel;